package com.huanghj.base.dao;


import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.huanghj.base.dto.PageModel;


/**
 * 
 * @author Hungel zou
 *
 */
@Repository
@SuppressWarnings({ "rawtypes", "unchecked" })
public abstract class BaseDao {
	
	// 本地可执行CRUD的数据源
	@Autowired  
	protected NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	
	@Autowired  
	protected JdbcTemplate jdbcTemplate;
	
	public Long getSequenceNexVal(String sequenceName) {
		return jdbcTemplate.queryForObject("select NEXTVAL('"+sequenceName+"')", Long.class);
	}
	
	/**
	 * get page list
	 * @param pageNum
	 * @param itemCount
	 * @param sql
	 * @param sqlParams
	 * @return
	 */
	public PageModel getPageList(int pageNum, int itemCount, String sql, Object[] sqlParams){
		 
		return getPageList(pageNum, itemCount, sql, sqlParams, null, null, null);
	}
	

	/**
	 * get page list
	 * @param pageNum start from 1
	 * @param itemCount
	 * @param sql
	 * @param sqlParams
	 * @param rowMapper
	 * @return
	 */
	public PageModel getPageList(Integer pageNum, Integer itemCount, String sql, Object[] sqlParams, RowMapper rowMapper){
		
		return getPageList(pageNum, itemCount, sql, sqlParams, null, null, rowMapper);	
	}	
	
	
	/**
	 * get page list
	 * @param pageNum start from 1
	 * @param itemCount
	 * @param sql
	 * @param sqlParams
	 * @param orderField
	 * @param orderType
	 * @return
	 */
	public PageModel getPageList(int pageNum, int itemCount, String sql, Object[] sqlParams, String orderField,String orderType){
		
		return getPageList(pageNum, itemCount, sql, sqlParams, orderField, orderType, null);
	}
	
	/**
	 * get page list
	 * @param pageNum start from 1
	 * @param itemCount
	 * @param sql
	 * @param sqlParams
	 * @param orderField
	 * @param orderType
	 * @param rowMapper
	 * @return
	 */
	public PageModel getPageList(Integer pageNum, Integer itemCount, String sql, Object[] sqlParams, String orderField,String orderType, RowMapper rowMapper){
		
		if (pageNum == null || pageNum < 1) {
			pageNum = 1;
		}
		if (itemCount == null || itemCount <= 0) {
			itemCount = 10;
		}
		
		//get the total count sql
		String getTotalSql = "SELECT COUNT(0) FROM (" + sql + ") V";
		
		//get the records sql
		if(StringUtils.isNotEmpty(orderField)){
			sql += " ORDER BY " + orderField + " " + orderType;
		}
		
		String getResultSql = buildPageSql(Integer.toString(pageNum), Integer.toString(itemCount), sql);
		
		int totalCount = jdbcTemplate.queryForObject(getTotalSql, sqlParams, Integer.class);
		
		List resultList = null;
		if(rowMapper == null){
			resultList =  jdbcTemplate.queryForList(getResultSql, sqlParams);
		}else{
			resultList =  jdbcTemplate.query(getResultSql, sqlParams, rowMapper);
		}
		
		PageModel pageModel = new PageModel(pageNum, itemCount, totalCount, resultList);
		
        return pageModel;
	
	}
	
	/**
	 * get page list
	 * @param pageNum start from 1
	 * @param itemCount
	 * @param sql
	 * @param sqlParams
	 * @param orderField
	 * @param orderType
	 * @param rowMapper
	 * @return
	 */
	public PageModel getPageListWithoutCount(Integer pageNum, Integer itemCount, String sql, Object[] sqlParams, String orderField,String orderType, RowMapper rowMapper){
		
		if (pageNum == null || pageNum < 1) {
			pageNum = 1;
		}
		if (itemCount == null || itemCount <= 0) {
			itemCount = 10;
		}
		
		//get the total count sql
//		String getTotalSql = "SELECT COUNT(0) FROM (" + sql + ") V";
		
		//get the records sql
		if(StringUtils.isNotEmpty(orderField)){
			sql += " ORDER BY " + orderField + " " + orderType;
		}
		
		String getResultSql = buildPageSql(Integer.toString(pageNum), Integer.toString(itemCount), sql);
		
//		int totalCount = jdbcTemplate.queryForInt(getTotalSql, sqlParams);
		int totalCount = 0;
		
		List resultList = null;
		if(rowMapper == null){
			resultList =  jdbcTemplate.queryForList(getResultSql, sqlParams);
		}else{
			resultList =  jdbcTemplate.query(getResultSql, sqlParams, rowMapper);
		}
		
		PageModel pageModel = new PageModel(pageNum, itemCount, totalCount, resultList);
		
        return pageModel;
	
	}
	
	/**
	 * get po by id
	 * @param <T>
	 * @param strSql
	 * @param id
	 * @param t
	 * @return
	 */
	public <T> T getPo(String strSql, Long id, T t){
		
		List<T> resultList = jdbcTemplate.query(strSql, new Object[]{id}, new BeanPropertyRowMapper(t.getClass()));
		if(resultList != null && resultList.size() > 0){
			return resultList.get(0);
		}else{
			return null;
		}
	}
	
	/**
	 * get po by id
	 * @param <T>
	 * @param strSql
	 * @param id
	 * @param t
	 * @return
	 */
	public <T> T getPo(String strSql, String id, T t){
		
		List<T> resultList = jdbcTemplate.query(strSql, new Object[]{id}, new BeanPropertyRowMapper(t.getClass()));
		if(resultList != null && resultList.size() > 0){
			return resultList.get(0);
		}else{
			return null;
		}
	}
	
	public <T> Long insertPo(String strSql, T t){
				
		KeyHolder keyHolder = new GeneratedKeyHolder(); 
		int i = namedParameterJdbcTemplate.update(strSql, new BeanPropertySqlParameterSource(t), keyHolder);
		if(i > 0){
			return (Long)keyHolder.getKey();
		}else{
			return null;
		}
	}
	
	public <T> boolean updatePo(String strSql, T t){
		
		int i = namedParameterJdbcTemplate.update(strSql, new BeanPropertySqlParameterSource(t));
		if(i > 0){
			return true;
		}else{
			return false;
		}
	}
	
	public boolean deletePo(String strSql, Long id){
		
		int i = jdbcTemplate.update(strSql, new Object[]{id});
		if(i > 0){
			return true;
		}else{
			return false;
		}
	}
	
	public boolean delete(String tabelName, String idColumn, Long id) throws Exception {
		return deletePo("delete from " + tabelName + " where " + idColumn + "=?", id);
	}
	
	protected String buildPageSql(String pageId, String itemCount,String strSql) {
		String returnStr = "";
		returnStr = strSql + " limit "+((Integer.parseInt(pageId)-1) * Integer.parseInt(itemCount))+","+itemCount;
		return returnStr;
	}
	
	public StringBuffer addTableFieldPrefix(String tableField, String prefix) {
		StringBuffer newStr = new StringBuffer(prefix+".");
		for (int i = 0; i < tableField.length(); i++) {
			char c = tableField.charAt(i);
			if (c == ',') {
				newStr.append("_").append(prefix).append(c).append(prefix).append(".");
			} else {
				newStr.append(c);
			}
		}
		newStr.append("_").append(prefix);
		return newStr;
	}
}
